package br.com.dreamsource.mobile.worders.forms.core;

import br.com.dreamsource.mobile.jmesql.exceptions.SQLException;
import br.com.dreamsource.mobile.jmesql.io.File;
import br.com.dreamsource.mobile.jmesql.jdbcConnection;
import br.com.dreamsource.mobile.jmesql.jdbcDriver;
import br.com.dreamsource.mobile.jmesql.jdbcPreparedStatement;
import br.com.dreamsource.mobile.jmesql.jdbcResultSet;
import br.com.dreamsource.mobile.jmesql.jdbcStatement;
import br.com.dreamsource.mobile.worders.pojo.UserPOJO;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

/**
 *
 * @author Elizeu Nogueira da Rosa Jr.
 * @version 0.1
 * @date 20.05.2006
 *
 */

public class DatabaseManager {
    
    private static jdbcConnection connection;
    private UserPOJO userPOJO;
    
    public DatabaseManager(UserPOJO userPOJO) throws SQLException {
        this.userPOJO = userPOJO;
        if (this.connection == null) {
            jdbcDriver driver = new jdbcDriver();
            this.connection = driver.connect(this.userPOJO.getAsProperties());
        }
    } 
    
    private jdbcConnection getConnection() throws SQLException {
        if (this.connection == null) {
            throw new SQLException("A conexao esta nula!");
        }
        return this.connection;
    }
    
    private jdbcPreparedStatement getPreparedStatement(String sqlValue) throws SQLException {
        return this.getConnection().prepareStatement(sqlValue);
    }
    
    public jdbcResultSet list(String sql) throws SQLException {
        jdbcPreparedStatement prepared = this.getPreparedStatement(sql);
        return prepared.executeQuery();
    }
    
    public jdbcResultSet findByField(String sql, String field, String value) throws SQLException {
        jdbcPreparedStatement prepared = this.getPreparedStatement(sql + " where "+field+" = ?");
        prepared.setString(1, value);
        return prepared.executeQuery();
    }
    
    public jdbcResultSet findByFieldLike(String sql, String field, String value) throws SQLException {
        jdbcPreparedStatement prepared = this.getPreparedStatement(sql + " where "+field+" like(?)");
        prepared.setString(1, "%"+value+"%");
        return prepared.executeQuery();
    }
    
    public jdbcResultSet find(String sqlValue, Hashtable attributes) throws SQLException {
        jdbcPreparedStatement prepared = this.getPreparedStatement(sqlValue);
        Enumeration en = attributes.elements();
        while (en.hasMoreElements()) {
            
        }
        return null;
    }
    
    public boolean delete(String sqlValue, Vector values) throws SQLException {
        return this.executeUpdateSQL(sqlValue, values);
    }
    
    public boolean update(String sqlValue, Vector values) throws SQLException {
        return this.executeUpdateSQL(sqlValue, values);
    }
    
    private boolean executeUpdateSQL(String sqlValue, Vector values) throws SQLException {
        jdbcPreparedStatement preparedStatement = this.getPreparedStatement(sqlValue);
        for (int i = 0; i < values.size(); i++) {
            preparedStatement.setString(i + 1, (String)values.elementAt(i));
        }
        if (preparedStatement.executeUpdate() > 0) {
            return true;
        } else {
            return false;
        }
    }
    
    public boolean insert(String sqlValue, Vector values) throws SQLException {
        return this.executeUpdateSQL(sqlValue, values);
    }
    
    //Metodos estaticos
    
    public static boolean databaseExists(String databaseName) {
        File f = new File(databaseName+".script");
        return f.exists();
    }
    
    public static void createDatabase(UserPOJO userPOJO) throws SQLException {
        jdbcDriver driver = new jdbcDriver();
        
        String userName = userPOJO.getUserName();
        String userPassword = userPOJO.getPasswd();
        
        userPOJO.setUserName("SA");
        userPOJO.setPasswd("");
        userPOJO.setDbName(Consts.DATABASE_NAME);
        
        jdbcConnection connection = driver.connect(userPOJO.getAsProperties());
        jdbcStatement statement = connection.createStatement();
        statement.execute(Consts.CREATE_PRICETABLE_TBL);
        statement.execute(Consts.CREATE_CUSTOMER_TBL);
        statement.execute("insert into priceTable values (null, 'tabela1', 10)");
        
        userPOJO.setUserName(userName);
        userPOJO.setPasswd(userPassword);
        
        /* Cria o novo usuario e remove o usuario padrao */
        jdbcPreparedStatement preparedStatement = connection.prepareStatement(Consts.CREATE_USER);
        preparedStatement.setString(1, userPOJO.getUserName());
        preparedStatement.setString(2, userPOJO.getPasswd());
        preparedStatement.executeUpdate();
        preparedStatement = connection.prepareStatement(Consts.DROP_USER);
        preparedStatement.setString(1, "SA");
        preparedStatement.executeUpdate();
    }
    
}
